In [114]:
%matplotlib inline
import csv
from tabulate import tabulate
import matplotlib.pylab as plt
import matplotlib as mpl
import pandas as pd
import pandas.io.sql as pandasql
import datetime
from psycopg2 import connect
In [31]:
#Setting up postgresql connection
con = connect(database='rdumas',host='localhost',port='5433',user='rdumas')
In [3]:
#Ostensibly prevents plotting when using the save_fig command, but I've seen no evidence of success
#http://stackoverflow.com/a/15713545/4047679
plt.ioff()
plt.style.use('ggplot')
In [4]:
'''
Setting colours
http://stackoverflow.com/a/37211181/4047679
Blue: #004B85
Red: #F2756D
'''
mpl.rcParams['axes.prop_cycle'] = mpl.cycler(color=['#004B85','#F2756D'])
In [32]:
sql = "SELECT dt_week::date, roadname as corridor, direction, period,tt_med as median_tt "\
"FROM rdumas.signal_timing_perf_alldata "\
"INNER JOIN rdumas.corridors_signal_timing_lookup USING (signal_timing_id) "\
"WHERE daytype = 'Midweek' and period IN ('AMPK','PMPK') "\
"ORDER BY roadname, direction, period, dt_week"
signals_perf = pandasql.read_sql(sql, con)
In [27]:
dirs = signals_perf.direction.unique()
peaks = signals_perf.period.unique()
corridors = pandasql.read_sql("SELECT roadname as corridor, direction FROM corridors_signal_timing_lookup", con)
In [15]:
#Signal retiming periods
retiming_dates = {'Markham Rd':dict(x1=datetime.date(2014,8,11), x2=datetime.date(2014,8,20), text='Signal\nRetiming'),
'Sheppard Ave':dict(x1=datetime.date(2014,7,22), x2=datetime.date(2014,7,25), text='Signal\nRetiming')}
In [43]:
'''Median Travel Time Plots'''
ylim=[0,40]
for corridor in signals_perf.corridor.unique():
fig, ax = plt.subplots(1,2, figsize=(16,4), sharey=True)
for ax_c, pk in enumerate(peaks):
for direction in corridors[corridors["corridor"]==corridor].direction.sort_values():
signals_perf[(signals_perf["corridor"]==corridor)
& (signals_perf["direction"]==direction)
& (signals_perf["period"]==pk)].plot(x='dt_week',
y='median_tt',
linewidth=2,
ax=ax[ax_c],
label=direction
)
ax[ax_c].set_title(pk)
ax[ax_c].set_autoscaley_on(False)
ax[ax_c].set_ylim(ylim)
ax[ax_c].axvspan(retiming_dates[corridor]['x1'], retiming_dates[corridor]['x2'], alpha=0.5, label=retiming_dates[corridor]['text'])
ax[ax_c].set_xlabel('Month')
ax[ax_c].xaxis.set_major_locator(mpl.dates.MonthLocator(bymonth=range(1,13,3)))
fig.suptitle(corridor, fontsize=16)
ax[0].set_ylabel('Median Travel Time (min)')
ax[0].legend().set_visible(False)
leg = ax[1].legend(loc='center right',bbox_to_anchor=(1.3,0.5))
for legobj in leg.legendHandles:
legobj.set_linewidth(6.0)
plt.gcf().autofmt_xdate()
plt.minorticks_off()
fig.savefig('corridors/signal_retiming/median_tt_plots/'+ corridor +'.png', format='png', transparent=True)
plt.close(fig)
In [44]:
sql = "SELECT dt_week::date, roadname as corridor, direction, period,tt_med as median_tt "\
"FROM rdumas.signal_timing_perf_score30 "\
"INNER JOIN rdumas.corridors_signal_timing_lookup USING (signal_timing_id) "\
"WHERE daytype = 'Midweek' and period IN ('AMPK','PMPK') "\
"AND dt_week < '2016-01-01'::date "\
"ORDER BY roadname, direction, period, dt_week"
signals_perf = pandasql.read_sql(sql, con)
In [45]:
dirs = signals_perf.direction.unique()
peaks = signals_perf.period.unique()
corridors = pandasql.read_sql("SELECT roadname as corridor, direction FROM corridors_signal_timing_lookup", con)
In [46]:
#Signal retiming periods
retiming_dates = {'Markham Rd':dict(x1=datetime.date(2014,8,11), x2=datetime.date(2014,8,20), text='Signal\nRetiming'),
'Sheppard Ave':dict(x1=datetime.date(2014,7,22), x2=datetime.date(2014,7,25), text='Signal\nRetiming')}
In [47]:
'''Median Travel Time Plots'''
ylim=[0,40]
for corridor in signals_perf.corridor.unique():
fig, ax = plt.subplots(1,2, figsize=(16,4), sharey=True)
for ax_c, pk in enumerate(peaks):
for direction in corridors[corridors["corridor"]==corridor].direction.sort_values():
signals_perf[(signals_perf["corridor"]==corridor)
& (signals_perf["direction"]==direction)
& (signals_perf["period"]==pk)].plot(x='dt_week',
y='median_tt',
linewidth=2,
ax=ax[ax_c],
label=direction
)
ax[ax_c].set_title(pk)
ax[ax_c].set_autoscaley_on(False)
ax[ax_c].set_ylim(ylim)
ax[ax_c].axvspan(retiming_dates[corridor]['x1'], retiming_dates[corridor]['x2'], alpha=0.5, label=retiming_dates[corridor]['text'])
ax[ax_c].set_xlabel('Month')
ax[ax_c].xaxis.set_major_locator(mpl.dates.MonthLocator(bymonth=range(1,13,3)))
fig.suptitle(corridor, fontsize=16)
ax[0].set_ylabel('Median Travel Time (min)')
ax[0].legend().set_visible(False)
leg = ax[1].legend(loc='center right',bbox_to_anchor=(1.3,0.5))
for legobj in leg.legendHandles:
legobj.set_linewidth(6.0)
plt.gcf().autofmt_xdate()
plt.minorticks_off()
fig.savefig('corridors/signal_retiming/median_tt_plots/'+ corridor +'score30.png', format='png', transparent=True)
plt.close(fig)
In [58]:
time_periods = pandasql.read_sql(' SELECT period, lower(period_range) AS starttime, upper(period_range) AS endtime'\
' FROM ref.timeperiod_ranges'\
' GROUP BY period ORDER BY starttime', con)
for idx, row in time_periods.iterrows():
print(idx, row['period'], row['starttime'])
In [116]:
'''Printing tables to csv'''
cur = con.cursor()
for corridor, signal_dict in retiming_dates.items():
cur.execute('SELECT direction FROM corridors_signal_timing_lookup WHERE roadname = %(roadname)s', {'roadname':corridor})
directions = cur.fetchall()
with open('corridors/signal_retiming/before-after-corridors.md', 'a') as f:
f.write('#{corridor} \n'.format(corridor=corridor))
for direction in directions:
sql = cur.mogrify(""" WITH before AS(
SELECT period, percentile_cont(0.5) WITHIN GROUP (ORDER BY corridor_tt) AS median_tt
FROM rdumas.corridors_signal_timing_tt2
INNER JOIN ref.timeperiod USING (time_15_continuous)
INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt))
INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id)
WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday)
AND roadname = %(corridor)s AND direction = %(direction)s
AND dt <@ daterange( (%(startdate)s - INTERVAL '1 month')::DATE, %(startdate)s)
GROUP BY period
), after AS(
SELECT period, percentile_cont(0.5) WITHIN GROUP (ORDER BY corridor_tt) AS median_tt
FROM rdumas.corridors_signal_timing_tt2
INNER JOIN ref.timeperiod USING (time_15_continuous)
INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt))
INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id)
WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday)
AND roadname = %(corridor)s AND direction = %(direction)s
AND dt <@ daterange( %(enddate)s, (%(enddate)s + INTERVAL '1 month')::DATE)
GROUP BY period
)
SELECT period,
to_char(before.median_tt, 'FM90.0') AS "Travel Time Before (min)",
to_char(after.median_tt, 'FM90.0') AS "Travel Time After(min)",
to_char(100*(after.median_tt - before.median_tt)/before.median_tt,'FMSG990.0')||'%%' AS "Percentage Change"
FROM before
INNER JOIN after USING (period)
INNER JOIN ref.timeperiod_ranges USING (period)
ORDER BY period_range""",
{'startdate':signal_dict['x1'], 'enddate':signal_dict['x2'], 'corridor':corridor, 'direction':direction[0]})
data = pandasql.read_sql(sql.decode('utf-8'), con)
with open('corridors/signal_retiming/before-after-corridors.md', 'a') as f:
f.write('##{direction}\n'.format(direction=direction[0]))
f.write(tabulate(data, headers="keys", tablefmt="pipe"))
f.write('\n')
cur.close()
In [83]:
print(sql)
In [77]:
cur = con.cursor()
for corridor, signal_dict in retiming_dates.items():
cur.execute('SELECT direction FROM corridors_signal_timing_lookup WHERE roadname = %(roadname)s', {'roadname':corridor})
directions = cur.fetchall()
for direction in directions:
print(corridor, direction[0])
In [99]:
con.rollback()
In [ ]:
'''Testing Whisker Plot'''
corridor = 'Markham Rd'
signal_dict = retiming_dates['Markham Rd']
time_periods = pand
fig, ax = plt.subplots(1,2, figsize=(16,4), sharey=True)
data_sql = "SELECT period, roadname as corridor, direction, corridor_tt, "\
"CASE WHEN dt < %(startdate)s THEN 'Before' "\
"WHEN dt > %(enddate)s THEN 'After' "\
"END as signal_period "\
"FROM rdumas.corridors_signal_timing_tt2 "\
"INNER JOIN ref.timeperiod USING (time_15_continuous) "\
"INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt)) "\
"INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id) "\
"WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday) "\
"AND roadname = %(corridor)s "\
"AND (dt <@ daterange( (%(startdate)s - INTERVAL '1 month')::DATE, %(startdate)s) "\
"OR dt <@ daterange(%(enddate)s,(%(enddate)s + INTERVAL '1 month')::DATE))"
whisker_data = pandasql.read_sql(data_sql, con,
params={'startdate':signal_dict['x1'], 'enddate':signal_dict['x2'], 'corridor':corridor})
directions = whisker_data.direction.unique()
for ax_c, dir in enumerate(directions):
ax[ax_c].boxplot()
whisker_data[]
plt.boxplot
In [52]:
for corridor, signal_dict in retiming_dates.items():
data_sql = "SELECT period, roadname as corridor, direction, corridor_tt, "\
"CASE WHEN dt < %(startdate)s THEN 'Before' "\
"WHEN dt > %(enddate)s THEN 'After' "\
"END as signal_period "\
"FROM rdumas.corridors_signal_timing_tt2 "\
"INNER JOIN ref.timeperiod USING (time_15_continuous) "\
"INNER JOIN ref.daytypes ON (isodow = EXTRACT('isodow' from dt)) "\
"INNER JOIN corridors_signal_timing_lookup USING (signal_timing_id) "\
"WHERE daytype = 'Midweek' AND dt NOT IN (SELECT dt FROM ref.holiday) "\
"AND roadname = %(corridor)s "\
"AND (dt <@ daterange( (%(startdate)s - INTERVAL '1 month')::DATE, %(startdate)s) "\
"OR dt <@ daterange(%(enddate)s,(%(enddate)s + INTERVAL '1 month')::DATE))"
whisker_date = pandasql.read_sql(data_sql, con,
params={'startdate':signal_dict['x1'], 'enddate':signal_dict['x2'], 'corridor':corridor})
dic
In [ ]:
""